Read all persons

using (EntitiesModel dbContext = new EntitiesModel())
{
  foreach (var person in dbContext.Persons)
  {
    string firstName = person.FirstName;
  }
}

More info and samples on: www.devarchweb.net

Add new person

using (EntitiesModel dbContext = new EntitiesModel())
{
  Persons newPerson = new Persons();
  newPerson.FirstName = "New";
  dbContext.Add(newPerson);
  dbContext.SaveChanges();
}

More info and samples on: www.devarchweb.net

Update Person with Id 1

using (EntitiesModel dbContext = new EntitiesModel())
{
  Persons person = dbContext.Persons.Where(p => p.Id==1).FirstOrDefault();
  person.LastName += "++";
  dbContext.SaveChanges();
}

More info and samples on: www.devarchweb.net

Delete persons with Id > 1

using (EntitiesModel dbContext = new EntitiesModel())
{
  IQueryable<Persons> personsToDelete = dbContext.Persons.Where(p => p.Id > 1);
  dbContext.Delete(personsToDelete);
  // dbContext.Delete(personsToDelete.FirstOrDefault()); One record can be deleted as well
  dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll);
}

More info and samples on: www.devarchweb.net

Concurency handling during save

// executes all SQL statements regardless of error
dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll);

// stops the processing after the first error occurs
dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.StopOnFirst);

More info and samples on: www.devarchweb.net

Automatic join with WHERE (persons and departments) Normally it possibl eto write join between 2 tables like below

var people2 = dbContext.Persons
  .Join(
  dbContext.Departments,
  p => p.Department_id,
  d => d.Id,
  (p, d) => new { name = p.FirstName, name1 = p.LastName, d.Name }
  );

Telerik offers another approach without JOIN clause

var people3 = from p in dbContext.Persons
       from d in dbContext.Departments
       where p.Department_id == d.Id // works like JOIN
       select new { p.FirstName, p.LastName, d.Name };

The syntax above will generate a SQL Statement with CROSS JOIN
(which with WHERE clause will behave like INNER JOIN)

SELECT a.[firstName] AS COL1, a.[lastName] AS COL2, b.[name] AS COL3
FROM [MY_SCHEMA].[persons] a
CROSS JOIN [MY_SCHEMA].[departments] AS b
WHERE a.[department_id] = b.[id]

More info and samples on: www.devarchweb.net

Use navigation property to read Department name for every person It is possible to use Navigation to access data in related tables

foreach (Persons person in dbContext.Persons)
{
string departmenName = person.Departments.Name;
}

It is necessary to be aware:
1. If DepartmentId is null then person.Departments.Name will throw an exception
2. Telerik will generate one SQL statement to retrieve Person data
and one SQL statement for every unique department ID.
If more people have the same Department_ID then Telerik will generate only one statement for the ID
as an argument for a stored procedure.

SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7
FROM [MY_SCHEMA].[persons] a
ORDER BY COL1

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2 FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=1
select @p1

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2 FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=2
select @p1

More info and samples on: www.devarchweb.net

Join with in memory list and data in DB (person and departments ids) Telerik enables writing queries that join data in memeory with data in SQL database

var persons5 = (from p in dbContext.Persons
        where p.FirstName == ("John")
        join d in deptIds on p.Department_id equals d
        select p)
        .ToList();

As you can see join happens in memory. Generated SQL statement:

More info and samples on: www.devarchweb.net

How will SQL query look like when WHERE in before or after JOIN) The example above shows that WHERE clause was present. It is the case only when WHERE clause
was present before before JOIN.

List<int> deptIds = new List<int> { 1, 2 }; // user selects ids in UI

var persons6 = (from p in dbContext.Persons
        join d in deptIds on p.Department_id equals d
        where p.FirstName == ("John")
        select p)
        .ToList();

When WHERE is after JOIN, it will be generated in SQL statement, which will cause performance problems with large data.

More info and samples on: www.devarchweb.net

When will ORDER BY (unexpectedly) present in SQL query

var persons12 = (from p in dbContext.Persons
select p
).ToList();

ORDER BY clause could cause perfromance problems with large data when for example data is queried
from a View and telerik picks a column that does not have an index.

If instead of full table specific column is specified

var persons13 = (from p in dbContext.Persons
select new { p.LastName }
).ToList();

then ORDER BY clause disaperars.

SELECT a.[lastName] AS COL1
FROM [MY_SCHEMA].[persons] a

If multiple columns are selected, ORDER BY is not present either.

var persons14 = (from p in dbContext.Persons
select new { p.FirstName, p.LastName }
).ToList();

SELECT a.[firstName] AS COL1, a.[lastName] AS COL2
FROM [MY_SCHEMA].[persons] a

If ORDER BY clause is added then Telerik will enearte SQL respecting that clause
and still adding autodetected column.

var persons15 = (from p in dbContext.Persons
         orderby p.LastName
         select p
        ).ToList();

More info and samples on: www.devarchweb.net

How will be join tables for N to relationship in model handled If you have two tables with n to n relationship like

CREATE TABLE [MY_SCHEMA].[persons](
[id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](20) NULL,
[lastName] [varchar](20) NOT NULL,
)

CREATE TABLE [MY_SCHEMA].[roles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL, -- e.g. Manager, Engineer, Accountant
[Description] [nvarchar](max) NULL,
)

and so called joined table that defines the relationship

CREATE TABLE [MY_SCHEMA].[persons_roles](
[Person_ID] [int] NOT NULL,
[Role_ID] [int] NOT NULL
)

then Telerik designer will NOT generate Entity for the helper table persons_roles.
Instead Telerik will generate navigation property Roles in Person(s) class

As result of that behavior, you need to write data that would normally required writing
a JOIN, you need to take a different approach. For example, if you want to read all employees
who belong to manager role you need to write query like this

var personsInRole = dbContext.Persons
.Where(p => p.Roles.Where(role => role.Name == "Manager").Count() > 0 );

If you need the helper table to be presented in data model, then if you add a dummy column to the table
then Telerik will generate Entity for the table. It of course mean, that query join queries
will not work anymore, because the naviagtion property disappears. You need to write standard joins.

More info and samples on: www.devarchweb.net